import {
  type BrowserWindow,
  app,
  ipcMain,
} from 'electron'
import path from 'node:path'
import {
  type Database,
  verbose,
} from 'sqlite3'
import { IPC } from 'root/common/constants'
import crypto from "crypto";

const TAG = '[sqlite3]'

interface loginObj {
  username: string;
  password: string;
}

interface modPasswordObj {
  password: string;
  uid: Number;
}

/**
 * Sql 类设计为单例模式
 */
export class Sql {
  private static instance: Sql

  public static getInstance(
    // 数据库文件地址
    filename = path.join(app.getPath('userData'), import.meta.env.VITE_SQL_FILENAME),
  ) {
    // console.log('filename', filename)
    // 首次调用会初始化
    return this.instance ??= new Sql(filename)
  }

  public database: Database

  private constructor(filename: string) {
    this.database = new (verbose().Database)(filename, error => {
      if (error) {
        console.log(TAG, '初始化失败')
        console.log(error)
      } else {
        console.log(TAG, '初始化成功')
      }
    })
  }

  /** 执行 sql 语句 */
  execute(sql: string, params?: any): Promise<any> {
    return new Promise((resolve, reject) => {
      const searchString: string = "select";
      const that = this
      if (sql.toLowerCase().includes(searchString)) {
        that.database.all(sql, params, function executeCb(error, row) {
          if (error) {
            reject(error);
          } else {
            resolve(row);
          }
        })
      } else {
        that.database.run(sql, params, function executeCb(error) {
          if (error) {
            reject(error);
          } else {
            resolve(this);
          }
        })
      }
    })
  }

  /** 执行 账号密码登录 方法 */
  userLogin(params: loginObj): Promise<any> {
    return new Promise((resolve, reject) => {
      params.password = crypto.createHash("sha256").update(params.password).digest("hex") 
      const sql:string = `
        SELECT * 
        FROM admin
        WHERE username='${params.username}' AND password='${params.password}';
      `;
      this.database.all(sql, function executeCb(error, row) {
        if (error) {
          reject(error)
        } else {
          resolve(row)
        }
      })
    })
  }

  /** 执行 修改密码 方法 */
  modPassword(params: modPasswordObj): Promise<any> {
    return new Promise((resolve, reject) => {
      params.password = crypto.createHash("sha256").update(params.password).digest("hex") 
      const sql:string = `
        UPDATE admin SET 
        password = '${params.password}' 
        WHERE uid=${params.uid};
      `;
      this.database.run(sql, function executeCb(error) {
        if (error) {
          reject(error)
        } else {
          resolve(this)
        }
      })
    })
  }
}






/** 初始化数据库 */
export function initDatabase() {
  const sql = Sql.getInstance()

  const checkTableExistsSQL = `
    SELECT name
    FROM sqlite_master
    WHERE type='table' AND name=?;
  `;
  sql.database.get(checkTableExistsSQL, ['admin'], function(err, row) {
    if (err) return console.error('Error:', err.message);
    if (row) return // admin表存在

    // admin表不存在， 创建表admin
    const createTableAdminSQL = `
      CREATE TABLE admin (
        uid INTEGER NOT NULL,
        username TEXT NOT NULL,
        password TEXT NOT NULL,
        name TEXT,
        activation_code TEXT,
        company TEXT,
        phone TEXT,
        logo TEXT,
        token TEXT,
        PRIMARY KEY (uid),
        UNIQUE (username)
      )
    `;
    sql.database.run(createTableAdminSQL, function(err) {
      if (err) return console.error('Error:', err.message);

      // 插入admin账号
      let object = {
        username: "admin", 
        password: "",
        name: "超级管理员",
        logo: "",
      }
      object.password = crypto.createHash("sha256").update('save123').digest("hex")

      const insertSQL = `
        INSERT INTO admin (username, password, name, logo)
        VALUES (?, ?, ?, ?)
      `;
      sql.database.run(insertSQL, [object.username, object.password, object.name, object.logo], (err) => {
        if (err) {
          console.error('Error inserting data:', err.message);
        } else {
          console.log('inserted user "admin" successfully!');
        }   
        // sql.database.close(); 
      })
    })
  });
}


/** 初始化数据表download */
export function initdownload() {
  const sql = Sql.getInstance()

  const checkTableExistsSQL = `
    SELECT name
    FROM sqlite_master
    WHERE type='table' AND name=?;
  `;
  sql.database.get(checkTableExistsSQL, ['download'], function(err, row) {
    if (err) return console.error('Error:', err.message);
    if (row) return // 表存在

    // admin表不存在， 创建表admin
    const createTableAdminSQL = `
    CREATE TABLE download(id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(255),type VARCHAR(255),type_id VARCHAR(255),cover VARCHAR(255),ste VARCHAR(255),time VARCHAR(255),path VARCHAR(255))
    `;
    sql.database.run(createTableAdminSQL, function(err) {
      if (err) return console.error('Error:', err.message);
    })
  });
}

 /** 执行更新下载状态 方法 "update download set ste="+ste+" where id="+id; */
 export function update_ste(id:number,ste:number) {
  const sql = Sql.getInstance()
  const insertSQL = "update download set ste="+ste+" where id="+id;
      sql.database.run(insertSQL, (err) => {
        if (err) {
          //console.error('Error inserting data:', err.message);
        } else {
          //console.log('inserted user "admin" successfully!');
        }   
        // sql.database.close(); 
      })
  
}




/** 初始化给渲染进程使用的 ipc 方法 */
export function initWithIpc(window?: BrowserWindow): Sql {
  const sql = Sql.getInstance()

  // 向渲染进程抛出 sqlite3 查询方法
  ipcMain.handle(IPC.SQL_EXEC, (event, sql2, params) => sql.execute(sql2, params))

  // 抛出账号密码登录sql查询
  ipcMain.handle(IPC.SQL_LOGIN, (event, params) => sql.userLogin(params))

  // 抛出修改密码
  ipcMain.handle(IPC.MODIFY_PASSWORD, (event, params) => sql.modPassword(params))

  return sql
}
